﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Model;
using System.Data.SqlClient;
namespace DAL
{
   public class 商品设置DAL
    {
           //查询所有商品类别
       public static DataSet ProductType() {

           return DBHelper.Duankaishi("select PTID,PTName from ProductType", null);
       }
       public static DataSet Product() {
           string sql = "select ProductID,ProductName,b.PTName,ProductPrice,ProductJP,b.PTID from Products a join ProductType b on a.PTID =b.PTID where a.PTID like '%'+@PTID+'%'";
       List<SqlParameter> list = new List<SqlParameter>(){
           new SqlParameter("@PTID",Products.PTID)
           };
       return DBHelper.Duankaishi(sql,list.ToArray());
       }
       //商品简拼查询
       public static DataSet ProductsChaxun()
       {
           string sql = "select ProductName,b.PTName,ProductPrice from Products a join ProductType b on a.PTID =b.PTID where ProductJP like @ProductJP+'%'";
           List<SqlParameter> list = new List<SqlParameter>(){
           new SqlParameter("@ProductJP",Products.ProductJP)
           };
           return DBHelper.Duankaishi(sql, list.ToArray());
       }
       //添加商品类别
       public static int ProductTypeAdd()
       {
           string sql = "insert into ProductType values(@PTName)";
           List<SqlParameter> list = new List<SqlParameter>(){
           new SqlParameter("@PTName",Products.PTName)
           };
           return DBHelper.Update(sql, list.ToArray());

       }
       //修改商品类别
       public static int ProductTypeUpdate()
       {
           string sql = "Update ProductType set PTName =@PTName where PTID=@PTID";
           List<SqlParameter> list = new List<SqlParameter>(){
           new SqlParameter("@PTName",Products.PTName),
            new SqlParameter("@PTID",Products.PTID)
           };
           return DBHelper.Update(sql, list.ToArray());

       }
       //查询商品类别的外键（查询表是否有数据）
       public static DataSet ProductTypeChuProducts()
       {
           string sql = "select * from Products a join ProductType b on a.PTID =b.PTID where a.PTID =@PTID";
           List<SqlParameter> list = new List<SqlParameter>(){
            new SqlParameter("@PTID",Products.PTID)
           };
           return DBHelper.Duankaishi(sql,list.ToArray());
       }
       //删除商品类别
       public static int ProductTypeDelete()
       {
           string sql = "delete ProductType where PTID = @PTID";
           List<SqlParameter> list = new List<SqlParameter>(){
            new SqlParameter("@PTID",Products.PTID)
           };
           return DBHelper.Update(sql, list.ToArray());
       }
       //删除商品表
       public static int ProductsDelete()
       {
           string sql = "delete Products where ProductID = @ProductID";
           List<SqlParameter> list = new List<SqlParameter>(){
            new SqlParameter("@ProductID",Products.ProductID)
           };
           return DBHelper.Update(sql, list.ToArray());
       }
       public static object ChaDan() {
           string sql = "select COUNT(*) from ProductType where PTName=@PTName";
           List<SqlParameter> list = new List<SqlParameter>(){
            new SqlParameter("@PTName",Products.PTName)
           };
           return DBHelper.Chadanzhi(sql, list.ToArray());
       }
    }
}
